package com.bayleaf.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bayleaf.core.BayleafAbstractDAO;
import com.bayleaf.domain.DictionaryVO;

public class BayleafDictionaryDAO extends BayleafAbstractDAO {

	/**
	 * 쿼리들
	 */
	// 영문 검색
	public static final String SQL_STD_WORDS_BY_EN_NM = "SELECT DICT_EN_NM, DICT_NM FROM TB_M_DICT WHERE DICT_EN_NM LIKE ?";
	// 한글로 검색
	public static final String SQL_STD_WORDS_BY_NM = "SELECT DICT_EN_NM, DICT_NM FROM TB_M_DICT WHERE DICT_NM LIKE ?";
	//전체
	public static final String SQL_STD_WORDS_ALL = "SELECT DICT_EN_NM, DICT_NM FROM TB_M_DICT ORDER BY 1";


	/**
	 * aWord : 검색어 opt : EN(영문단어로), KO(한국어)
	 *
	 * @param aWord
	 * @return
	 */
	public List<DictionaryVO> getStdNameListByNm(String aWord, String opt)
			throws Exception {

		// 단어에 null들어오면 "" 로 검색되도록
		aWord = aWord == null ? "" : aWord;

		List<DictionaryVO> dictWordlist = new ArrayList<DictionaryVO>();

		// 검색어는 무조건 대문자로 변환
		aWord = aWord.toUpperCase();
		String EXEC_SQL;
		if ("EN".equals(opt)) {
			EXEC_SQL = SQL_STD_WORDS_BY_EN_NM;
		} else if ("KO".equals(opt)) {
			EXEC_SQL = SQL_STD_WORDS_BY_NM;
		} else {
			EXEC_SQL = SQL_STD_WORDS_ALL;
		}

		ResultSet rs = ExcuteQuery(EXEC_SQL, aWord + "%");
		DictionaryVO dictionaryVO = null;

		while (rs.next()) {
			dictionaryVO = new DictionaryVO();
			dictionaryVO.setStdName(rs.getString("DICT_NM"));
			dictionaryVO.setStdNameEng(rs.getString("DICT_EN_NM"));

			dictWordlist.add(dictionaryVO);
		}

		rs.close();

		return dictWordlist;
	}
	
	public List<DictionaryVO> getStdNameListAll()
			throws Exception {
		String EXEC_SQL=SQL_STD_WORDS_ALL;
		
		ResultSet rs = ExcuteQuery(EXEC_SQL);
		List<DictionaryVO> dictWordlist = new ArrayList<DictionaryVO>();
//		List<String> al = new ArrayList<String>();
		DictionaryVO dictionaryVO = null;
		while (rs.next()) {
			dictionaryVO = new DictionaryVO();
			dictionaryVO.setStdName(rs.getString("DICT_NM"));
			dictionaryVO.setStdNameEng(rs.getString("DICT_EN_NM"));
//			al.add(rs.getString("DICT_NM") + ":" + rs.getString("DICT_EN_NM"));
			dictWordlist.add(dictionaryVO);
		}
		rs.close();
		
		return dictWordlist;
	}

	/**
	 * aWord : 검색어 opt : EN(영문단어로), KO(한국어)
	 *
	 * @param aWord
	 * @return
	 */
	public void insertStdNameSet(String [] col1, String [] col2)
	throws Exception {

		// 검색어는 무조건 대문자로 변환
		String EXEC_SQL = "INSERT INTO TB_M_DICT (DICT_NO, DICT_EN_NM, DICT_NM, DICT_DESC, USE_YN, REG_DATE) " +
				"VALUES (NEXT VALUE FOR TB_M_DICT_SEQ,?,?,?,'Y',sysdate)";

		for(int i =0 ; i < col1.length; i++){
			System.out.println(i+"th row insertion");
			ExcuteQuery(EXEC_SQL, col1[i].toUpperCase(), col2[i].toUpperCase(), col1[i]);
		}
	}

}
